package dao;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

public class HomeDao extends BaseDao {

    public Map<String, Object> findHomeCount() {

        String sql = "  SELECT\n" +
                "        ( SELECT count( 1 ) FROM USER WHERE DATE_FORMAT( register_time, '%Y-%m-%d' )= CURRENT_DATE ) userCount,\n" +
                "        ( SELECT count( 1 ) FROM article WHERE DATE_FORMAT( publish_date, '%Y-%m-%d' )= CURRENT_DATE ) articleCount,\n" +
                "        ( SELECT count( 1 ) FROM meeting WHERE DATE_FORMAT( publish_date, '%Y-%m-%d' )= CURRENT_DATE ) meetingCount";

        return jdbcTemplate.queryForMap(sql);
    }

    public List<List<Integer>> findHomeDetail() {
        String sql = "\n" +
                "select \n" +
                "(select count(1) from user where DATE_FORMAT(register_time,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-6)) day6,\n" +
                "(select count(1) from user where DATE_FORMAT(register_time,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-5)) day5,\n" +
                "(select count(1) from user where DATE_FORMAT(register_time,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-4)) day4,\n" +
                "(select count(1) from user where DATE_FORMAT(register_time,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-3)) day3,\n" +
                "(select count(1) from user where DATE_FORMAT(register_time,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-2)) day2,\n" +
                "(select count(1) from user where DATE_FORMAT(register_time,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-1)) day1,\n" +
                "(select count(1) from user where DATE_FORMAT(register_time,'%Y-%m-%d')=CURRENT_DATE) today\n" +
                "\n" +
                "\n" +
                "union all\n" +
                "\n" +
                "\n" +
                "select \n" +
                "(select count(1) from article where DATE_FORMAT(publish_date,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-6)) day6,\n" +
                "(select count(1) from article where DATE_FORMAT(publish_date,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-5)) day5,\n" +
                "(select count(1) from article where DATE_FORMAT(publish_date,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-4)) day4,\n" +
                "(select count(1) from article where DATE_FORMAT(publish_date,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-3)) day3,\n" +
                "(select count(1) from article where DATE_FORMAT(publish_date,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-2)) day2,\n" +
                "(select count(1) from article where DATE_FORMAT(publish_date,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-1)) day1,\n" +
                "(select count(1) from article where DATE_FORMAT(publish_date,'%Y-%m-%d')=CURRENT_DATE) today\n" +
                "\n" +
                "\n" +
                "union all\n" +
                "\n" +
                "\n" +
                "select \n" +
                "(select count(1) from meeting where DATE_FORMAT(publish_date,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-6)) day6,\n" +
                "(select count(1) from meeting where DATE_FORMAT(publish_date,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-5)) day5,\n" +
                "(select count(1) from meeting where DATE_FORMAT(publish_date,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-4)) day4,\n" +
                "(select count(1) from meeting where DATE_FORMAT(publish_date,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-3)) day3,\n" +
                "(select count(1) from meeting where DATE_FORMAT(publish_date,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-2)) day2,\n" +
                "(select count(1) from meeting where DATE_FORMAT(publish_date,'%Y-%m-%d')=ADDDATE(CURRENT_DATE,-1)) day1,\n" +
                "(select count(1) from meeting where DATE_FORMAT(publish_date,'%Y-%m-%d')=CURRENT_DATE) today\n" +
                "\n";


        //List<List<T>>

        ResultSetExtractor<List<List<Integer>>> resultSetExtractor = new ResultSetExtractor<List<List<Integer>>>() {

            @Override
            public List<List<Integer>> extractData(ResultSet rs) throws SQLException, DataAccessException {
                List<List<Integer>> resultData = new ArrayList<>();

                while (rs.next()) {

                    //一行的数据
                    List<Integer> rowData = new ArrayList<>();

                    rowData.add(rs.getInt("day6"));
                    rowData.add(rs.getInt("day5"));
                    rowData.add(rs.getInt("day4"));
                    rowData.add(rs.getInt("day3"));
                    rowData.add(rs.getInt("day2"));
                    rowData.add(rs.getInt("day1"));
                    rowData.add(rs.getInt("today"));

                    //把这一行的数据添加到大的集合
                    resultData.add(rowData);
                }
                return resultData;
            }
        };

        return jdbcTemplate.query(sql, resultSetExtractor);
    }
}
